# !/usr/bin/python
# -*- conding:utf-8 -*-
import pymssql
from bottle import *  # 导入bottle相关的包
import time
import json
app = Bottle()
#debug state
Go = True
def debug(d):
    if(Go):
        print(d)
def debug_dict(d):
    Go = True
    if(Go):
        for _d in d:
            print(_d)
class DB:
    state = False
    cursor = None
    #order number
    ONO = None
    def get_next_ono(self):
        if(self.state):
            self.ONO+=1
        else:
            self.get_cursor()
        return self.ONO.__str__()

    def get_cursor(self):
        if(self.state):
            return self.cursor
        else:
            server = "DESKTOP-J5O06U8\SQLEXPRESS"
            passwd = "123456"
            user = "marc"
            database = "SPJ"
            try:
                conn = pymssql.connect(server, user, passwd, database)
                self.cursor = conn.cursor(as_dict=True)
                if self.cursor:
                    self.state = True
                    self.ONO = int(time.time())
                    return self.cursor
                else: raise("fail connect db srv1")
            except Exception:
                raise("fail to connect srv2")
                self.state = False
                self.cursor = None
        raise ("fail to connect srv2")
        return None
    def exe(self,sql):
        if not self.state:
            self.cursor = self.get_cursor()

        if not self.state:
            raise("error exe")
            return None

        print(sql);

        self.cursor.execute(sql)

        ret =self.cursor.fetchall()
        #print(ret)
        return  ret
class API:

    def no_fun(self):
        raise("no function")

    def exe(self,ret,func="no_fun",data=None):
        fun = self.__getattribute__(func)
        fun(ret,data)

    def get_table(self,ret,data):
        tb_name = data['t_name']
        tb_name = tb_name.__str__()
        ret['data'] = db.exe('SELECT * FROM '+tb_name)
        if tb_name== "Orders":
            for r in ret['data']:
                print(r)
                r['OTIME'] = r['OTIME'].__str__()

    def creat_order(self,ret,data):
        for key in data:
            data[key] = data[key].__str__()
            data[key]=data[key].strip()


        cost = (eval(data['N'])*eval(data['B'])).__str__()
        ono = db.get_next_ono()


        sql = ("CreateOrder '" + ono
                + "','"+ data['S']
                + "','"+ data['P']
                + "','"+ data['J']
                + "','"+ cost
                + "',"+data['N']
                + ";")
        sql = " DECLARE @state int;EXECUTE @state =" + sql + ";SELECT	'state' = @state;"
        print(sql)
        res = db.exe(sql)
        print(res)
        if not res[0]['state'] :
            ret['data'] = "order create success,cost:"+cost
        else:
            ret['code'] = "12321"
            ret['desc']="store process fail!"



db = DB()
api = API()

@app.route('/',methods=['GET','POST'])
@view('init')
def init():
    db.get_cursor()
    state = db.state
    return dict(state = state)


@app.route('/index')
@view('index')
def index():
    ret = {}
    ret['S'] = db.exe("SELECT * FROM S;")
    ret['tables'] =db.exe("select name from sysobjects where xtype='U';")
    ret['P'] = db.exe("SELECT * FROM P;")
    ret['J'] = db.exe("SELECT * FROM J;")
    ret['B'] = db.exe("SELECT * FROM balance;")
    for r in ret:
        print(r)
    return dict(ret)


@app.route('/static/<path:path>')
def server_static(path):
    return static_file(path,root='./static')

@app.route("/API/<api_name>",method=['GET','POST'])
def _api(api_name):
    ret = {
        "code":0,
        "data":"no data",
        "desc":"right"
    }
    if not hasattr(api,api_name):
        ret['code'] = 404
        ret['desc'] = "no api respnse"
        return dict(ret)
    parameter = request.POST.get('data')
    parameter = json.loads(parameter)

    if not parameter:
        ret['code'] = 405
        ret['desc'] = "need parameter"
    else:
        print(parameter)
        api.exe(ret,api_name,parameter)

    print(ret)
    return dict(ret)



@app.error(404)
def _error(error):
    return "Sorry nothing for you!"



run(app,host='0.0.0.0', port=80,debug=True)


